<?php
 
class DB_Functions 
{
 
    private $db;
 
    //put your code here
    // constructor
    function __construct() 
    {
        require_once 'DB_Connect.php';
        // connecting to database
        $this->db = new DB_Connect();
        $this->db->connect();
    }
 
    // destructor
    function __destruct() 
    {
        $this->db->close();
    }

    public function getItemById($item_id) 
    {
        $sitem_id=$this->db->sanitizeInput($item_id);
        $sql=mysql_query("SELECT * FROM Item WHERE item_id= '".$sitem_id."' ");
        while($row=mysql_fetch_assoc($sql)){
            $loc=$this->computeItemLocation($row['item_id']);
            $r=array_merge($row,$loc);
            $result[]=$r;
        }
        return $result;
    }


    public function getPlaceById($place_id) 
    {
        $splace_id=$this->db->sanitizeInput($place_id);
        $sql=mysql_query("SELECT * FROM Place WHERE place_id= '".$splace_id."' ");
        while($row=mysql_fetch_assoc($sql))
            $result[]=$row;
        return $result;
    }

  public function searchItems($item_name) 
    {
        $sitem_name=$this->db->sanitizeInput($item_name);
        $sql=mysql_query("SELECT * FROM Item WHERE MATCH (item_name,item_descrip) AGAINST ('".$sitem_name."')");
        while($row=mysql_fetch_assoc($sql)){
            $loc=$this->computeItemLocation($row['item_id']);
            $r=array_merge($row,$loc);
            $result[]=$r;
        }

        $sql=mysql_query("SELECT * FROM Item INNER JOIN lkup_Tag_Item ON lkup_Tag_Item.item_id =   Item.item_id WHERE lkup_Tag_Item.tag_id = '".$sitem_name."' ");
           while($row=mysql_fetch_assoc($sql)){
                   $loc=$this->computeItemLocation($row['item_id']);
                    $r=array_merge($row,$loc);
                   $category_result[]=$r;
             }
        if ($result == null)
        {
              $final_array = $category_result;
        }
        else if ($category_result == null)
        {
             $final_array = $result;
        }
        else
        {
             $final_array =array_merge($result,$category_result);
        }
        return $final_array;
    }

    public function searchItemsByPlace($item_name,$place_id) 
    {
        $sitem_name=$this->db->sanitizeInput($item_name);
        $splace_id=$this->db->sanitizeInput($place_id);
        $sql=mysql_query("SELECT * FROM Item WHERE (MATCH (item_name,item_descrip) AGAINST ('".$sitem_name."') OR item_name='".$sitem_name."' OR item_descrip='".$sitem_name."')AND Item.place_id='".$splace_id."'");
        while($row=mysql_fetch_assoc($sql)){
            $loc=$this->computeItemLocation($row['item_id']);
            $r=array_merge($row,$loc);
            $result[]=$r;
        }

        $sql=mysql_query("SELECT * FROM Item INNER JOIN lkup_Tag_Item ON lkup_Tag_Item.item_id =   Item.item_id WHERE lkup_Tag_Item.tag_id = '".$sitem_name."' AND Item.place_id='".$splace_id."' ");
           while($row=mysql_fetch_assoc($sql)){
                   $loc=$this->computeItemLocation($row['item_id']);
                    $r=array_merge($row,$loc);
                   $category_result[]=$r;
             }
        if ($result == null)
        {
              $final_array = $category_result;
        }
        else if ($category_result == null)
        {
             $final_array = $result;
        }
        else
        {
             $final_array =array_merge($result,$category_result);
        }
        return $final_array;
    }

   
public function searchPlaces($place_name)
    {
        $splace_name=$this->db->sanitizeInput($place_name);
        $sql = mysql_query("SELECT * FROM Place WHERE MATCH (place_name,place_descrip) AGAINST ('".$splace_name."')");
        $count = 0;
        while($row=mysql_fetch_assoc($sql))
         {
            $result[]=$row;
         }
         
        $sql=mysql_query("SELECT * FROM Place INNER JOIN lkup_Tag_Place ON lkup_Tag_Place.place_id = Place.place_id WHERE lkup_Tag_Place.tag_id = '".$splace_name."' ");
        while($row=mysql_fetch_assoc($sql))
        {
             $category_result[]=$row;
        }
        
        if ($result == null)
        {
              $final_array=$category_result;
        }
        else if ($category_result == null)
        {
             $final_array=$result;
        }
        else
        {
             $final_array=array_merge($result,$category_result);
        }
        return $final_array;
    }

    public function getPlaceMaps($place_id)
    {
        $splace_id=$this->db->sanitizeInput($place_id);
        $result = mysql_query("SELECT * FROM PlaceMaps WHERE place_id='".$splace_id."'");
        while($row=mysql_fetch_assoc($sql))
            $result[]=$row;
        return $result;
    }

    public function getPlaceMapByID($place_id)
    {
        $splace_id=$this->db->sanitizeInput($place_id);
        $result = mysql_query("SELECT * FROM PlaceMaps WHERE place_id = '".$splace_id."'"); 
        $row = mysql_fetch_assoc($result);
        $bin = base64_encode($row['image']);
        $object[] = $bin;
        return $object;
    }

    public function getItemList($user_id,$place_id)
    {
        $suser_id=$this->db->sanitizeInput($user_id);
        $splace_id=$this->db->sanitizeInput($place_id);
        $sql = mysql_query("SELECT Item.* FROM Item Inner Join ItemList ON Item.item_id=ItemList.item_id Where ItemList.user_id='$suser_id' AND Item.place_id='$splace_id'"); 
        while($row=mysql_fetch_assoc($sql)){
            $loc=$this->computeItemLocation($row['item_id']);
            $r=array_merge($row,$loc);
            $result[]=$r;
        }
        return $result;
    }

    public function addtoItemList($user_id,$item_id)
    {
        $suser_id=$this->db->sanitizeInput($user_id);
        $sitem_id=$this->db->sanitizeInput($item_id);
        $sql=mysql_query("INSERT INTO ItemList (item_id,user_id) VALUES ('$sitem_id','$suser_id')");
        if($sql){
            return true;
        }else{
            return false;
        }
    }

    public function getPlaceList($user_id)
    {
        $suser_id=$this->db->sanitizeInput($user_id);
        $sql = mysql_query("SELECT Place.* FROM Place Inner Join PlaceList ON Place.place_id = PlaceList.place_id Where PlaceList.user_id='$suser_id'"); 
        while($row=mysql_fetch_assoc($sql))
            $result[]=$row;
        return $result;
    }

    public function addtoPlaceList($user_id,$place_id)
    {
        $suser_id=$this->db->sanitizeInput($user_id);
        $splace_id=$this->db->sanitizeInput($place_id);
        $sql=mysql_query("INSERT INTO PlaceList (place_id,user_id) VALUES ('$splace_id','$suser_id')");
        if($sql){
            return true;
        }else{
            return false;
        }
    }
    
    public function addItem($item_name,$item_descrip,$place_id,$loc_x,$loc_y,$loc_z,$user_id)
    {
        $sitem_name=$this->db->sanitizeInput($item_name);
        $sitem_descrip=$this->db->sanitizeInput($item_descrip);
        $splace_id=$this->db->sanitizeInput($place_id);
        $sloc_x=$this->db->sanitizeInput($loc_x);
        $sloc_y=$this->db->sanitizeInput($loc_y);
        $sloc_z=$this->db->sanitizeInput($loc_z);
        $suser_id=$this->db->sanitizeInput($user_id);
        $ur=mysql_query("SELECT uuid() as item_id");
        $ur=mysql_fetch_array($ur);
        $item_id=$ur['item_id'];
        $q= 'INSERT INTO Item (item_id,item_name,item_descrip,place_id,user_id) VALUES (\''.$item_id.'\',\''.$sitem_name.'\',\''.$sitem_descrip.'\',\''.$splace_id.'\',\''.$suser_id.'\')';
        $result=mysql_query($q);
        $r=$this->addItemLocation($item_id,$sloc_x,$sloc_y,$sloc_z,1);
        if($result){
            return $item_id;
        }else{
            return false;
        }
    }
    public function addItemWithTimeout($item_name,$item_descrip,$place_id,$loc_x,$loc_y,$loc_z,$user_id,$timeout)
    {
        $sitem_name=$this->db->sanitizeInput($item_name);
        $sitem_descrip=$this->db->sanitizeInput($item_descrip);
        $splace_id=$this->db->sanitizeInput($place_id);
        $sloc_x=$this->db->sanitizeInput($loc_x);
        $sloc_y=$this->db->sanitizeInput($loc_y);
        $sloc_z=$this->db->sanitizeInput($loc_z);
        $suser_id=$this->db->sanitizeInput($user_id);
        $stimeout=$this->db->sanitizeInput($timeout);
        $ur=mysql_query("SELECT uuid() as item_id");
        $ur=mysql_fetch_array($ur);
        $item_id=$ur['item_id'];
        $q= 'INSERT INTO Item (item_id,item_name,item_descrip,place_id,user_id,timeout) VALUES (\''.$item_id.'\',\''.$sitem_name.'\',\''.$sitem_descrip.'\',\''.$splace_id.'\',\''.$suser_id.'\','.$stimeout.')';
        $result=mysql_query($q);
        $r=$this->addItemLocation($item_id,$sloc_x,$sloc_y,$sloc_z,1);
        if($result){
            return $item_id;
        }else{
            return false;
        }
    }

    public function addItemLocation($item_id,$loc_x,$loc_y,$loc_z,$weight){
        $sitem_id=$this->db->sanitizeInput($item_id);
        $sloc_x=$this->db->sanitizeInput($loc_x);
        $sloc_y=$this->db->sanitizeInput($loc_y);
        $sloc_z=$this->db->sanitizeInput($loc_z);
        $sweight=$this->db->sanitizeInput($weight);
        $q='INSERT INTO ItemLocation (item_id,loc_x,loc_y,loc_z,weight) VALUES (\''.$sitem_id.'\','.$sloc_x.','.$sloc_y.','.$sloc_z.','.$sweight.')';
        $sql=mysql_query($q);
        if($sql){
            return true;
        }else{
            return false;
        }
    }

    public function computeItemLocation($item_id){

        $get_locs_query='SELECT * FROM ItemLocation WHERE item_id=\''.$item_id.'\'';
        $sql=mysql_query($get_locs_query);
        $sx=0.0;
        $sy=0.0;
        $sz=0.0;
        $sw=0.0;
        while($row=mysql_fetch_assoc($sql)) {
            $w=$row['weight'];
            $sw=$sw+$w;
            $sx=$sx+$row['loc_x']*$w;
            $sy=$sy+$row['loc_y']*$w;
            $sz=$sz+$row['loc_z']*$w;
        }

        $result['loc_x']=$sx/$sw;
        $result['loc_y']=$sy/$sw;
        $result['loc_z']=floor($sz/$sw);
        return $result;
    }

    public function radiusSearch($lat,$lon,$dist){
         $slat=$this->db->sanitizeInput($lat);
         $slon=$this->db->sanitizeInput($lon);
         $sdist=$this->db->sanitizeInput($dist);
         $q='SELECT  * FROM Place WHERE ((ACOS(SIN('.$slat.' * PI() / 180) * SIN(place_lat * PI() / 180) + COS('.$slat.' * PI() / 180) * COS(place_lat * PI() / 180) * COS(('.$slon.'-place_long) * PI() / 180)) * 180 / PI()) * 60 * 1.1515)<='.$dist;
        $sql=mysql_query($q);
        while($row=mysql_fetch_assoc($sql))
            $result[]=$row;
        return $result;
    }

    public function storeUser($name, $email, $password) 
    {
        $sname=$this->db->sanitizeInput($name);
        $semail=$this->db->sanitizeInput($email);
        $spassword=$this->db->sanitizeInput($password);
        $uuid = uniqid('', true);
        $hash = $this->hashSSHA($spassword);
        $encrypted_password = $hash["encrypted"]; // encrypted password
        $salt = $hash["salt"]; // salt
        $result = mysql_query("INSERT INTO users(unique_id, name, email, encrypted_password, salt, created_at) VALUES('$uuid', '$sname', '$semail', '$encrypted_password', '$salt', NOW())");
        // check for successful store
    if ($result) 
    {
            // get user details
            $uid = mysql_insert_id(); // last inserted id
            $result = mysql_query("SELECT * FROM users WHERE uid = '$uid'");
            // return user details
            return mysql_fetch_array($result);
    } 
    else 
    {
            return false;
        }
    }
 
    //Get user by email and password
    public function getUserByEmailAndPassword($email, $password) {
        $semail=$this->db->sanitizeInput($email);
        $spassword=$this->db->sanitizeInput($password);
        $result = mysql_query("SELECT * FROM users WHERE email = '$semail'") or die(mysql_error());
        // check for result
        $no_of_rows = mysql_num_rows($result);
        if ($no_of_rows > 0) {
            $result = mysql_fetch_array($result);
            $salt = $result['salt'];
            $encrypted_password = $result['encrypted_password'];
            $hash = $this->checkhashSSHA($salt, $spassword);
            // check for password equality
            if ($encrypted_password == $hash) {
                // user authentication details are correct
                return $result;
            }
        } else {
            // user not found
            return false;
        }
    }
 
    /**
     * Check user is existed or not
     */
    public function isUserExisted($email) {
        $semail=$this->db->sanitizeInput($email);
        $result = mysql_query("SELECT email from users WHERE email = '$semail'");
        $no_of_rows = mysql_num_rows($result);
        if ($no_of_rows > 0) {
            // user existed
            return true;
        } else {
            // user not existed
            return false;
        }
    }
 
    /**
     * Encrypting password
     * @param password
     * returns salt and encrypted password
     */
    public function hashSSHA($password) {
 
        $salt = sha1(rand());
        $salt = substr($salt, 0, 10);
        $encrypted = base64_encode(sha1($password . $salt, true) . $salt);
        $hash = array("salt" => $salt, "encrypted" => $encrypted);
        return $hash;
    }
 
    /**
     * Decrypting password
     * @param salt, password
     * returns hash string
     */
    public function checkhashSSHA($salt, $password) {
        $hash = base64_encode(sha1($password . $salt, true) . $salt);
        return $hash;
    }
    
     /**
     * Add place name, description, and map from website
     */
     public function addPlaceMaps($place_name, $place_description, $user_id, $longtitude, $latitude, $data) {
        $ur=mysql_query("SELECT uuid() as place_id");
        $ur=mysql_fetch_array($ur);
        $place_id=$ur['place_id'];
        $splace_name=$this->db->sanitizeInput($place_name);
        $splace_description=$this->db->sanitizeInput($place_description);
        $suser_id=$this->db->sanitizeInput($user_id);
        $slongtitude=$this->db->sanitizeInput($longtitude);
        $slatitude=$this->db->sanitizeInput($latitude);
        $sdata=$this->db->sanitizeInput($data);
        $sql=mysql_query("INSERT INTO PlaceMaps (pmap_id, place_id, image) VALUES (uuid(), '" . $place_id . "', '" . $sdata . "')");
        //$sql=mysql_query("INSERT INTO PlaceMaps (pmap_id, place_id, image) VALUES (uuid(), 'a', '" . $sdata . "')");
        //$sql=mysql_query("INSERT INTO Place (place_id, place_name, place_descrip, user_id, place_lat, place_long) VALUES ('" . $place_id . "', '" . $splace_name . "', '" . $splace_description . "', '" . $suser_id . "', " . (float) $slongtitude . ", " . (float) $slatitude . ")");
        $sql=mysql_query("INSERT INTO Place (place_id, place_name, place_descrip, user_id, place_lat, place_long) VALUES ('" . $place_id . "', '" . $splace_name . "', '" . $splace_description . "', '" . $suser_id . "', " . (float) $slatitude . ", " . (float) $slongtitude . ")");
        if($sql){
            return true;
        }else{
            return false;
        }
        
     }
 
}
 
?>
